"""
发货表统计处理 - 使用openpyxl重新实现
功能：
1. 根据发货表模板创建按商品名称分类的发货表
2. 从订单表中查询对应商品的订单信息（商品名称，店长ID维度）
3. 计算填充发货数量
4. 合并相同商品名称的表头单元格
"""

import os
import re
from openpyxl import load_workbook, Workbook
from openpyxl.styles import Alignment, Font, PatternFill, Border, Side
from openpyxl.utils.dataframe import dataframe_to_rows
from datetime import datetime
import pandas as pd

class DeliveryTableProcessor:
    def __init__(self):
        self.current_dir = os.path.dirname(os.path.abspath(__file__))
        
        # 配置文件路径
        self.delivery_template_path = os.path.join(self.current_dir, 'C.PA直播间-新商城发货表_模板.xlsx')
        self.order_table_path = r'e:\vx\xwechat_files\wxid_xwi9k2ekef8422_e5fc\msg\file\2025-10\2025-10-17-订单列表2025-10-17 14_01_23.xlsx'
        
        # 订单信息配置
        self.order_info = [
            {
                '产品名称': '晚安优品冬夏两用防螨垫（绑带款）_150*200*0.5',
                '单位': '个',
                '数量': '1',
                '直播价': '299元/个',
                '规格': '150*200*0.5',
            },
            {
                '产品名称': '晚安优品冬夏两用防螨垫（绑带款）_180*200*0.5',
                '单位': '个',
                '数量': '1',
                '直播价': '299元/个',
                '规格': '180*200*0.5',
            },
            {
                '产品名称': '晚安优品乳胶枕头',
                '单位': '个',
                '数量': '2',
                '直播价': '680元/2个',
                '规格': '56*36*9/11（93±3%纯胶）',
            },
            {
                '产品名称': '晚安优品乳胶床垫1.8米',
                '直播价': '个',
                '数量': '1',
                '单位': '个',
                '规格': '',
            },
            {
                '产品名称': '晚安优品乳胶床垫1.5米',
                '直播价': '个',
                '数量': '1',
                '单位': '个',
                '规格': '',
            }
        ]
        
        # 是否清理商品名称中的特殊字符
        self.clear_name = False
        
    def extract_unit(self, spec=''):
        """获取规格中的单位"""
        return spec
    
    def get_delivery_quantity(self, product_name='', shop_id=''):
        """
        获取指定商品和店铺的发货数量
        
        参数:
            product_name (str): 商品名称
            shop_id (str): 店铺ID
            
        返回:
            int: 发货数量总和，默认为0
        """
        try:
            # 读取订单表Excel文件
            schedule_df = pd.read_excel(self.order_table_path)
            
            if self.clear_name:
                # 清理商品名称中的特殊字符
                schedule_df['商品名称'] = schedule_df['商品名称'].str.replace('_x000D_', '')
                schedule_df['商品名称'] = schedule_df['商品名称'].str.replace('\n', '')
                schedule_df['商品名称'] = schedule_df['商品名称'].str.replace('\s+', '', regex=True)
            
            # 解析商品名称和规格
            product_spec = product_name.split('_')[-1] if '_' in product_name else None
            base_product_name = product_name.split('_')[0] if '_' in product_name else product_name
            
            # 根据条件筛选数据
            if product_spec:
                product_row = schedule_df[
                    (schedule_df['商品名称'] == base_product_name) & 
                    (schedule_df['店长ID'] == shop_id) & 
                    (schedule_df['商品规格'] == product_spec)
                ]
            else:
                product_row = schedule_df[
                    (schedule_df['商品名称'] == base_product_name) & 
                    (schedule_df['店长ID'] == shop_id)
                ]
            
            # 如果找到匹配的数据行
            if not product_row.empty:
                return int(product_row['数量'].sum(skipna=True))
            
            print(f'未找到商品名称为"{product_name}",店铺id为：{shop_id},商品规格为：{product_spec}"的发货数量')
            return 0
            
        except Exception as e:
            print(f"获取发货数量时出错：{e}")
            return 0
    
    def create_delivery_table_with_template(self):
        """
        使用模板创建发货表
        
        返回:
            tuple: (workbook, worksheet) - 工作簿和工作表对象
        """
        try:
            # 加载模板文件
            wb = load_workbook(self.delivery_template_path)
            ws = wb.active
            
            # 获取模板的列数
            template_cols = ws.max_column
            
            # 为每个订单产品添加新列
            current_col = template_cols + 1
            
            for order_item in self.order_info:
                product_name = order_item['产品名称']
                
                # 添加产品名称作为列标题
                ws.cell(row=1, column=current_col, value=product_name)
                
                # 设置规格（第2行）
                ws.cell(row=2, column=current_col, value=order_item['规格'])
                
                # 设置单位（第3行）
                ws.cell(row=3, column=current_col, value=f"单位：{order_item['单位']}")
                
                current_col += 1
            
            # 添加发货信息列
            ws.cell(row=1, column=current_col, value="运单号")
            ws.cell(row=1, column=current_col + 1, value="运费") 
            ws.cell(row=1, column=current_col + 2, value="物流公司")
            
            return wb, ws
            
        except Exception as e:
            print(f"创建发货表模板时出错：{e}")
            # 如果模板文件不存在，创建新的工作簿
            wb = Workbook()
            ws = wb.active
            ws.title = "发货表"
            return wb, ws
    
    def fill_delivery_data(self, wb, ws):
        """
        填充发货数据
        
        参数:
            wb: 工作簿对象
            ws: 工作表对象
        """
        try:
            # 获取数据区域范围（从第4行开始，跳过前3行的标题信息）
            start_row = 4
            max_row = ws.max_row
            max_col = ws.max_column
            
            # 获取产品名称列的索引
            product_cols = {}
            for col in range(7, max_col - 2):  # 从产品列开始，排除最后3列（运单号、运费、物流公司）
                cell_value = ws.cell(row=1, column=col).value
                if cell_value and cell_value != "运单号":
                    product_cols[cell_value] = col
            
            # 遍历每一行（店铺）
            for row in range(start_row, max_row + 1):
                # 获取店铺ID
                shop_id_cell = ws.cell(row=row, column=2)  # 假设ID在第2列
                if not shop_id_cell.value:
                    continue
                    
                try:
                    shop_id = str(int(shop_id_cell.value))
                except (ValueError, TypeError):
                    continue
                
                # 为每个产品填充发货数量
                for product_name, col_idx in product_cols.items():
                    quantity = self.get_delivery_quantity(product_name, shop_id)
                    ws.cell(row=row, column=col_idx, value=quantity)
            
            # 添加总计行
            self.add_total_row(ws, max_row + 1, product_cols)
            
        except Exception as e:
            print(f"填充发货数据时出错：{e}")
    
    def add_total_row(self, ws, total_row, product_cols):
        """
        添加总计行
        
        参数:
            ws: 工作表对象
            total_row: 总计行号
            product_cols: 产品列字典
        """
        try:
            # 设置总计标题
            ws.cell(row=total_row, column=1, value="总计")
            
            # 设置总计样式
            total_cell = ws.cell(row=total_row, column=1)
            total_cell.font = Font(bold=True)
            total_cell.alignment = Alignment(horizontal='center')
            
            # 计算每个产品的总计
            for product_name, col_idx in product_cols.items():
                # 获取数量范围（从第4行到总计行-1）
                sum_formula = f"=SUM({ws.cell(row=4, column=col_idx).coordinate}:{ws.cell(row=total_row-1, column=col_idx).coordinate})"
                ws.cell(row=total_row, column=col_idx, value=sum_formula)
                
                # 设置总计单元格样式
                total_product_cell = ws.cell(row=total_row, column=col_idx)
                total_product_cell.font = Font(bold=True)
                total_product_cell.alignment = Alignment(horizontal='center')
            
        except Exception as e:
            print(f"添加总计行时出错：{e}")
    
    def merge_same_product_headers(self, ws):
        """
        合并相同商品名称的表头单元格
        
        参数:
            ws: 工作表对象
        """
        try:
            # 获取产品列范围
            max_col = ws.max_column - 3  # 排除最后3列（运单号、运费、物流公司）
            
            # 收集产品信息
            product_info = {}
            
            for col in range(7, max_col + 1):
                cell_value = ws.cell(row=1, column=col).value
                if cell_value:
                    # 提取基础产品名称（去掉规格部分）
                    base_name = cell_value.split('_')[0] if '_' in cell_value else cell_value
                    
                    if base_name not in product_info:
                        product_info[base_name] = []
                    product_info[base_name].append(col)
            
            # 合并相同产品名称的列
            for base_name, cols in product_info.items():
                if len(cols) > 1:
                    # 合并单元格
                    ws.merge_cells(
                        start_row=1, start_column=min(cols),
                        end_row=1, end_column=max(cols)
                    )
                    
                    # 设置合并后的值
                    ws.cell(row=1, column=min(cols), value=base_name)
                    
                    # 设置合并单元格样式
                    merged_cell = ws.cell(row=1, column=min(cols))
                    merged_cell.alignment = Alignment(horizontal='center', vertical='center')
                    merged_cell.font = Font(bold=True)
            
        except Exception as e:
            print(f"合并表头时出错：{e}")
    
    def apply_formatting(self, ws):
        """
        应用格式设置
        
        参数:
            ws: 工作表对象
        """
        try:
            # 设置标题行样式
            for col in range(1, ws.max_column + 1):
                cell = ws.cell(row=1, column=col)
                if cell.value:
                    cell.font = Font(bold=True, size=12)
                    cell.alignment = Alignment(horizontal='center', vertical='center')
                    cell.fill = PatternFill(start_color="CCCCCC", end_color="CCCCCC", fill_type="solid")
            
            # 设置规格和单位行样式
            for row in [2, 3]:
                for col in range(1, ws.max_column + 1):
                    cell = ws.cell(row=row, column=col)
                    if cell.value:
                        cell.alignment = Alignment(horizontal='center', vertical='center')
                        cell.font = Font(size=10, italic=True)
            
            # 设置边框
            thin_border = Border(
                left=Side(style='thin'),
                right=Side(style='thin'),
                top=Side(style='thin'),
                bottom=Side(style='thin')
            )
            
            # 为数据区域添加边框
            for row in range(1, ws.max_row + 1):
                for col in range(1, ws.max_column + 1):
                    ws.cell(row=row, column=col).border = thin_border
            
            # 自动调整列宽
            # for col in range(1, ws.max_column + 1):
            #     column_letter = ws.cell(row=1, column=col).column_letter
            #     max_length = 0
            #     for row in range(1, ws.max_row + 1):
            #         cell_value = str(ws.cell(row=row, column=col).value or '')
            #         if len(cell_value) > max_length:
            #             max_length = len(cell_value)
                
            #     adjusted_width = min(max_length + 2, 50)  # 最大宽度50
            #     ws.column_dimensions[column_letter].width = adjusted_width
            
        except Exception as e:
            print(f"应用格式时出错：{e}")
    
    def process_delivery_table(self):
        """
        主处理函数：处理完整的发货表流程
        """
        try:
            print("开始处理发货表...")
            
            # 步骤1：创建发货表模板
            print("1. 创建发货表模板...")
            wb, ws = self.create_delivery_table_with_template()
            
            # 步骤2：填充发货数据
            print("2. 填充发货数据...")
            self.fill_delivery_data(wb, ws)
            
            # 步骤3：合并相同产品表头
            print("3. 合并相同产品表头...")
            self.merge_same_product_headers(ws)
            
            # 步骤4：应用格式设置
            print("4. 应用格式设置...")
            self.apply_formatting(ws)
            
            # 步骤5：保存文件
            output_file = os.path.join(self.current_dir, '发货表统计_openpyxl.xlsx')
            wb.save(output_file)
            
            print(f"处理完成！文件已保存为：{output_file}")
            return output_file
            
        except Exception as e:
            print(f"处理发货表时出错：{e}")
            import traceback
            traceback.print_exc()
            return None
    
    def create_sample_data(self):
        """
        创建示例数据用于测试
        """
        # 示例店铺数据
        sample_shops = [
            {'店铺名称': '测试店铺1', 'ID': 1001},
            {'店铺名称': '测试店铺2', 'ID': 1002},
            {'店铺名称': '测试店铺3', 'ID': 1003},
        ]
        
        return sample_shops

def main():
    """主函数"""
    processor = DeliveryTableProcessor()
    
    # 处理发货表
    result_file = processor.process_delivery_table()
    
    if result_file:
        print(f"\n✅ 发货表处理成功！")
        print(f"📁 输出文件：{result_file}")
    else:
        print(f"\n❌ 发货表处理失败！")

if __name__ == '__main__':
    main()